ALTER FUNCTION [dbo].[CalInterestWkly]
(
	@report_month INTEGER,
	@report_year INTEGER,
	@principal DECIMAL(18, 2),
	@interest_rate DECIMAL(18, 8),
	@loan_start_date DATETIME,	
	@term INTEGER,	
	@payment_amount DECIMAL(18, 2)	
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

DECLARE @interest DECIMAL(18, 8) = 0,
	@payment_date DATETIME = DATEADD(wk, 1, @loan_start_date), --set first payment date 1 week afte disbursement date
	@counter INTEGER = 1,
	@report_date DATETIME 
	
SET @report_date = dbo.DateFromParts --end of the the month for which the report is required
(
	@report_year, 
	@report_month, 
	DAY(DATEADD(mm, DATEDIFF(mm, -1, dbo.DateFromParts(@report_year, @report_month, 1)), -1))
)	

SET @interest_rate = (@interest_rate / 100) / 52 --weekly interest rate

/*ensure that atleast one payment exist before the report date*/
IF @payment_date < @report_date
BEGIN
	/*loops through reducing the balance each week from the start of the loan until the month before the report*/
	WHILE (YEAR(@payment_date) < @report_year OR (YEAR(@payment_date) = @report_year AND MONTH(@payment_date) < @report_month))
	BEGIN	
		IF @counter <= @term --ensure that interest is calculated not exeeding the number of terms
		BEGIN
			SET @interest = @principal * @interest_rate  --calculate weekly interest
			SET @principal = @principal + @interest - @payment_amount --reduce the balance
			SET @payment_date = DATEADD(wk, 1, @payment_date)--set next payment date- 1 week ahead
			SET @counter = @counter + 1
		END	
		ELSE
		BEGIN
			BREAK
		END	
	END	
	
	SET @interest = 0 --reset interest- disregarding interest for months before the report
	
	/*Compute interest for the month of the report. The month of the payment date is the same as the month of the report*/
	WHILE @payment_date <= @report_date
	BEGIN
		IF @counter <= @term
		BEGIN
			SET @interest = @interest + (@principal * @interest_rate)  --calculate weekly interest
			SET @principal = @principal + (@principal * @interest_rate) - @payment_amount -- reduce the balance
			SET @payment_date = DATEADD(wk, 1, @payment_date) --set next payment date - 1 week ahead and to break loop
			SET @counter = @counter + 1
		END	
		ELSE
		BEGIN
			BREAK
		END	
	END
END
RETURN @interest --return the interest for the month
END;

